package videowarehouseapp.dataobjects;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;
import videostoreapp.Order.OrderInfo;
import videostoreapp.Order.OrderLineInfo;


import videowarehouseapp.ApplicationException;
import videowarehouseapp.Constants;
//import videowarehouseapp.Customer;
import videowarehouseapp.Delivery;
import videowarehouseapp.DeliveryMgr;
import videowarehouseapp.Stock;
//import videowarehouseapp.Order;
//import videowarehouseapp.Customer.CustomerInfo;
//import videowarehouseapp.Order.OrderInfo;
//import videowarehouseapp.Order.OrderLineInfo;

public class DeliveryDO implements Delivery{

	Connection conn = null; 
	PreparedStatement 
		psNewDlvr = null, 
		psUpdDlvr = null,
		psDelDlvr = null,
		psGetDlvr = null,
                psCheck = null;
	
	public DeliveryDO(){
		try {
			conn = Constants.getConnection();
			
			psNewDlvr = conn.prepareStatement(
					"INSERT INTO Deliveries (order_id, warehouse, delivery_date, status) VALUES (?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			
			psUpdDlvr = conn.prepareStatement(
					"UPDATE Deliveries SET order_id = ?, warehouse = ?, delivery_date= ?, status = ? WHERE ID = ?");
			
			psDelDlvr = conn.prepareStatement(
					"DELETE FROM Deliveries WHERE ID = ?");
			
			psGetDlvr = conn.prepareStatement(
					"SELECT id, order_id, warehouse, delivery_date, status FROM Deliveries WHERE ID = ?");
			
                        psCheck = conn.prepareStatement(
                                        "SELECT id FROM Deliveries WHERE order_id = ?");

                } catch (SQLException e) {
			Constants.SQLExceptionHandler(e);
		}
	}


        public int createDelivery(OrderInfo order) {
            int dlvrId=-1;
            try {
                psCheck.setInt(1, order.id);
                ResultSet rs = psCheck.executeQuery();
                if (rs.next()){
                    return rs.getInt("id");
                }
            }catch (SQLException sqe) {
                DeliveryMgr.getInstance().putBackMessage(order);
                Constants.SQLExceptionHandler(sqe);
             }
            OrderLineInfo[] videos=order.videos;
            String warehouse=order.warehouse;
            Stock s=new StockDO();
            boolean enoughStock=true;
            for (OrderLineInfo i:videos){
                int catalogue_id=i.catalogue_id;
                int quantity=i.quantity;
                try {
                    int sold = s.sold(catalogue_id, quantity, warehouse);
                }catch (ApplicationException ex) {
                    enoughStock=false;
                }
            }
            try{
                    psNewDlvr.setInt(1, order.id);
                    psNewDlvr.setString(2, order.warehouse);
                    psNewDlvr.setTimestamp(3,new Timestamp(Calendar.getInstance().getTimeInMillis()) );
                    if (enoughStock){
                        psNewDlvr.setInt(4, OrderInfo.OrderStatus.ACCEPTED);
                    }else{
                        psNewDlvr.setInt(4, OrderInfo.OrderStatus.BACKORDER);
                    }
                    if(psNewDlvr.executeUpdate() >0){
                        ResultSet rs = psNewDlvr.getGeneratedKeys();
                        if(rs.next()){
                            dlvrId = rs.getInt(1);
                        }
                        rs.close();
                    }
              }catch(SQLException sqe){
                    Constants.SQLExceptionHandler(sqe);
                    DeliveryMgr.getInstance().putBackMessage(order);
              }
            return dlvrId;
        }
       

        public void deleteDelivery(int id) {
		try{
			psDelDlvr.setInt(1, id);
			psDelDlvr.executeUpdate();
			
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
	}

	public DeliveryInfo getDeliveryById(int id) {
		DeliveryInfo dif = null;
		try{
			psGetDlvr.setInt(1, id);
			ResultSet rs = psGetDlvr.executeQuery();
			if(rs.next()){
				dif = new DeliveryInfo(
						id,
					rs.getInt("order_id"),
					rs.getString("warehouse"),
					rs.getTimestamp("delivery_date"),
					rs.getInt("status")
				);
				
			}
			rs.close();
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
		return dif;
	}

	public void modifyDelivery(DeliveryInfo dinfo) {
		if(dinfo == null) return;
		
		try{
			psUpdDlvr.setInt(1, dinfo.orderId);
			psUpdDlvr.setString(2, dinfo.warehouse);
			psUpdDlvr.setTimestamp(3, dinfo.deliveryDate);
			psUpdDlvr.setInt(4, dinfo.status);
			psUpdDlvr.setInt(5, dinfo.id);
			psUpdDlvr.executeUpdate();
			
		}catch(SQLException sqe){
			Constants.SQLExceptionHandler(sqe);
		}
	}

	public void submitDelivery(int id) throws ApplicationException{
               DeliveryInfo di=this.getDeliveryById(id);
               di.status=OrderInfo.OrderStatus.DELIVERED;
               di.deliveryDate=new Timestamp(Calendar.getInstance().getTimeInMillis());
               this.modifyDelivery(di);
               DeliveryMgr.getInstance().sendDeliveryMessage(di);
	}
	
}
